	/**
 * User数据模型模块
 * model\sqlite\user.js
 */
 
//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");
 
class UserDB{
	/**
   * @constructor
   * @private
   */
	constructor() {
		this.dbFile = config.dbfile;
		this.instance = null;
		this.db = null;
		this.register=this.add;
	}
	 
	/**
	* 创建UserDB对象
	* @returns {UserDB} UserDB实例
	*/
	static getInstance() {
		if (!this.instance) {
		  this.instance = new UserDB();
		}
		return this.instance;
	  }
	 
	  /**
	   * 连接数据库
	   * @returns {Promise}
	   */
	  connect() {
		return new Promise((resolve, reject) => {
		  this.db = new sqlite3.Database(this.dbFile, (err) => {
			if (err) {
			  reject(err);
			} else {
			  resolve("connect ok.");
			}
		  });
		});
	  }
	  /**
	   * 关闭数据库
	   * @returns  {Promise}
	   */
	  close() {
		return new Promise((resolve, reject) => {
		  this.db.close((err) => {
			if (err) {
			  reject(err);
			} else {
			  resolve("close ok.");
			}
		  });
		});
	  }
	 
	  /**
	   * 获取指定ID的用户信息
	   * @param {Number} userId 用户ID
	   * @returns  {Promise}
	   */
	  find(userId) {
		return new Promise((resolve, reject) => {
		  let sql = "SELECT * FROM users WHERE id = ?";
		  let params = [userId];
		  this.db.get(sql, params, (err, result) => {
			if (err) {
			  reject(err);
			} else {
			  resolve(result);
			}
		  });
		});
	  }
	 
	  /**
	   * 获取用户信息列表
	   * @param {Number} limit 数量
	   * @param {Number} offset 开始
	   * @returns  {Promise}
	   */
	  findAll(limit = -1, offset = -1) {
		return new Promise((resolve, reject) => {
		  let sql = "";
		  let params = [];
		  if (limit === -1) {
			sql = "SELECT * FROM users ORDER BY id";
		  } else if (offset === -1) {
			sql = "SELECT * FROM users ORDER BY id LIMIT ?";
			params[0] = limit;
		  } else {
			sql = "SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?";
			params[0] = limit;
			params[1] = offset;
		  }
		  this.db.all(sql, params, (err, result) => {
			if (err) {
			  reject(err);
			} else {
			  resolve(result);
			}
		  });
		});
	  }
	 
	  /**
	   * 新增用户
	   * @param {Object} user 用户数据
	   * @returns  {Promise}
	   */
	  add(user) {
		return new Promise((resolve, reject) => {
		  let sql = `INSERT INTO users (
				username, password, nickname, truename, avatar,
				role, last_login_time, last_login_ip, created_time, created_ip, 
				updated_time
			  ) VALUES (
				  ?, ?, ?, ?, ?,
				  ?, ?, ?, ?, ?,
				  ?
			  );`;
		  let params = [
			user.username,
			user.password,
			user.nickname,
			user.truename,
			user.avatar,
			user.role,
			user.lastLoginTime,
			user.lastLoginIP,
			user.createdTime,
			user.createdIP,
			user.updatedTime,
		  ];
	 
		  this.db.run(sql, params, function (err, result) {
			if (err) {
			  reject(err);
			} else {
			  util.log(this);
			  resolve(this.lastID); //插入的数据的自增ID
			}
		  });
		});
	  }
	 
	  /**
	   * 修改用户
	   * @param {Object} user 用户数据
	   * @returns  {Promise}
	   */
	  update(user) {
		return new Promise((resolve, reject) => {
		  let sql = `UPDATE users SET
				  username = ?, password = ?, nickname = ?, truename = ?, 
				  avatar = ?, role = ?, updated_time = ?
				  WHERE id = ?
			  ;`;
		  let params = [
			user.username,
			user.password,
			user.nickname,
			user.truename,
			user.avatar,
			user.role,
			user.updatedTime,
			user.id,
		  ];
	 
		  this.db.run(sql, params, function (err, result) {
			if (err) {
			  reject(err);
			} else {
			  resolve(this.changes); //影响的记录数
			}
		  });
		});
	  }
	 
	  /**
	   * 删除用户
	   * @param {Number} userId 用户ID
	   * @returns  {Promise}
	   */
	  remove(userId) {
		return new Promise((resolve, reject) => {
		  let sql = "DELETE FROM users WHERE id = ?";
		  let params = [userId];
		  this.db.run(sql, params, function (err, result) {
			if (err) {
			  reject(err);
			} else {
			  resolve(this.changes); //影响的记录数
			}
		  });
		});
	  }

	/**
	 * 获取用户数量
	 * @returns  {Promise}
	 */
	getCount() {
		return new Promise((resolve, reject) => {
		let sql = "SELECT count(1) AS total FROM users";
		let params = [];
		this.db.get(sql, params, function (err, result) {
			if (err) {
			reject(err);
			} else {
			resolve(result);
			}
		});
		});
	}

	 /**
   * 搜索用户
   * @param {String} q 查询关键字
   * @param {Number} limit 数量
   * @param {Number} offset 偏移
   * @returns {Promise} 
   */
	 search(q, limit = -1, offset = -1) {
		return new Promise((resolve, reject) => {
		  let sql = "";
		  q = `%${q}%`;
		  let params = [q, q, q];
		  if (limit === -1) {
			sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ?";
		  } else if (offset === -1) {
			sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ? LIMIT ?";
			params.push(limit);
		  } else {
			sql = "SELECT * FROM users  WHERE username LIKE ? OR nickname LIKE ? OR truename = ? LIMIT ? OFFSET ?";
			params.push(limit);
			params.push(offset);
		  }
		  this.db.all(sql, params, (err, result) => {
			if (err) {
			  util.err(err);
			  reject(err);
			} else {
			  resolve(result);
			}
		  });
		});
	  }

	  /**
   * 获取指定ID的用户信息
   * @param {String} username 用户名
   * @param {String} password 密码
   * @returns  {Promise}
   */
	  login(username, password) {
		return new Promise((resolve, reject) => {
		  let sql = "SELECT * FROM users WHERE username = ? AND password = ?";
		  let params = [username, password];
		  this.db.get(sql, params, (err, result) => {
			if (err) {
			  util.err(err);
			  reject(err);
			} else {
			  resolve(result);
			}
		  });
		});
	  }
	 
	  /**
	   * 更新用户登录相关数据
	   * @param {String} username 用户名
	   * @param {Object} lastLogin 登录状态对象 
	   * @returns {Promise}
	   */
	  touch(username, lastLogin) {
		return new Promise((resolve, reject) => {
		  let sql = `UPDATE users SET 
					last_login_time = ? , last_login_ip = ?, 
					login_count = login_count + 1 
					WHERE username = ?`;
		  let params = [lastLogin.time, lastLogin.ip, username];
		  this.db.run(sql, params, (err, result) => {
			if (err) {
			  util.err(err);
			  reject(err);
			} else {
			  resolve(this.changes);
			}
		  });
		});
	 
	  }

	    /**
   * 修改用户个人信息
   * @param {Object} user 用户数据
   * @returns  {Promise}
   */
  changeInfo(id, info) {
    return new Promise((resolve, reject) => {
      let sql = `UPDATE users SET nickname = ?, truename = ?,  avatar = ? WHERE id = ?;`;
      let params = [
        info.nickname,
        info.truename,
        info.avatar,
        id,
      ];
 
      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.changes); //影响的记录数
        }
      });
    });
  }

    /**
   * 修改用户密码
   * @param {Number} id 用户id
   * @param {String} password 用户密码
   * @returns  {Promise}
   */
	changePassword(id, password) {
		return new Promise((resolve, reject) => {
		  let sql = `UPDATE users SET password = ? WHERE id = ?;`;
		  let params = [password, id];
	 
		  this.db.run(sql, params, function (err, result) {
			if (err) {
			  util.err(err);
			  reject(err);
			} else {
			  resolve(this.changes); //影响的记录数
			}
		  });
		});
	  }
}
 
module.exports = UserDB;
